The present invention relates to database management systems and more particularly to improving throughput of transactions by reducing lock contention of concurrent transactions on the rows of a table in a relational data base system.
The rules that influence SQL result set creation semantics are defined in the SQL standard (ANSI Document X3.135-1986, “Database Language SQL, American Standards Institute, New York, 1986; ISO Document ISO/TC 97/SC, 21/WG 3, N 117). These rules comprise locking isolation levels which determine when an application program can retrieve data during a transaction. In general, the rules specify whether the result set consists of the committed rows only or whether it can include the rows that were uncommitted at the time of result set creation. Typical examples of the former are Cursor Stability (CS) and Read Stability (RS), and for the later Uncommitted Read (UR).
Many applications require committed read semantics which inevitably comes with the price of increased locking contention. In order to return a committed row the transaction might need to acquire a lock on it that is incompatible with locks requested by other concurrent transactions. With the known locking semantics the committed read transactions might requests and held locks even on the rows that do not qualify for the result set by means of the statement predicates. Apart from the overhead of acquiring and releasing these seemingly unnecessary locks this behavior increases the number of lock request suspensions and in some cases results in deadlocks and abnormal transaction terminations.
Accordingly, what is needed is a system and method for improving the throughput of transactions. In particular, what is needed is a system and method for reducing lock contentions in operations that require committed read semantics, and reducing deadlock situations in transactions. The present invention addresses such a need.